global root "C:\Users\chupilkm\EBRD\OCE - Maxim\War data\Earthquakes replication"



foreach var in "gdppc" "export_growth" "import_growth" {

clear
use "$root\Data\Working data\earthquake_`var'_dataset.dta"

drop if iso_code == "VNM" & earthquake_start == 1970

gen balanced_sample=1 if growth_pre_earthquake_5!=. & growth_pre_earthquake_4!=. & growth_pre_earthquake_3!=. & growth_pre_earthquake_2!=. & growth_pre_earthquake_1!=. & growth_earthquake!=. & growth_post_earthquake_1!=. & growth_post_earthquake_2!=. & growth_post_earthquake_3!=. & growth_post_earthquake_4!=. & growth_post_earthquake_5 != .

keep if balanced_sample==1

*************************************************************************ALL earthquakeS*******************************************************************************

preserve
collapse growth* comp*
export excel using "$root\Charts in excel\Figure 1.xlsx", sheet("`var'") sheetmodify cell(B3)
restore

preserve
collapse (count) growth_earthquake
export excel using "$root\Charts in excel\Figure 1.xlsx", sheet("`var'") sheetmodify cell(B28)
restore

preserve
gen GDP_index_pre_1 = 100
forvalues i = 2(1)5 {
    local a = `i' - 1
    gen GDP_index_pre_`i' = GDP_index_pre_`a'/(100+growth_pre_earthquake_`a')*100

}
gen index_earthquake = GDP_index_pre_1*(100+growth_earthquake)/100
gen GDP_index_post_1 = index_earthquake*(100+growth_post_earthquake_1)/100
forvalues i = 2(1)5 {
    local a = `i' - 1
    gen GDP_index_post_`i' = GDP_index_post_`a'*(100+growth_post_earthquake_`a')/100

}
order GDP_index_pre_5 GDP_index_pre_4 GDP_index_pre_3 GDP_index_pre_2 GDP_index_pre_1 index_earthquake GDP_index_post_1 GDP_index_post_2 GDP_index_post_3 GDP_index_post_4 GDP_index_post_5
gen a = 1
collapse (sd) GDP_index* (sd) index_earthquake* (sum) a
forvalues i = 5(-1)1 {
	gen conf_int_pre_`i' = GDP_index_pre_`i'*1.645/sqrt(a-1)
}
gen conf_int_earthquake = index_earthquake*1.645/sqrt(a-1)
forvalues i = 1(1)5 {
	gen conf_int_post_`i' = GDP_index_post_`i'*1.645/sqrt(a-1)
}
keep conf*
export excel using "$root\Charts in excel\Figure 1.xlsx", sheet("`var'") sheetmodify cell(B5)
restore
}

foreach var in "debt_to_GDP" "csh_i" "csh_g"{

clear
use "$root\Data\Working data\earthquake_`var'_dataset.dta"

gen balanced_sample=1 if growth_pre_earthquake_5!=. & growth_pre_earthquake_4!=. & growth_pre_earthquake_3!=. & growth_pre_earthquake_2!=. & growth_pre_earthquake_1!=. & growth_earthquake!=. & growth_post_earthquake_1!=. & growth_post_earthquake_2!=. & growth_post_earthquake_3!=. & growth_post_earthquake_4!=. & growth_post_earthquake_5 != .

keep if balanced_sample==1

*************************************************************************ALL earthquakeS*******************************************************************************

preserve
collapse growth* comp*
export excel using "$root\Charts in excel\Figure 1.xlsx", sheet("`var'") sheetmodify cell(B3)
restore

preserve
collapse (count) growth_earthquake
export excel using "$root\Charts in excel\Figure 1.xlsx", sheet("`var'") sheetmodify cell(B28)
restore

preserve
gen GDP_index_pre_1 = growth_pre_earthquake_1
forvalues i = 2(1)5 {
    local a = `i' - 1
    gen GDP_index_pre_`i' = growth_pre_earthquake_`i'

}
gen index_earthquake = growth_earthquake
gen GDP_index_post_1 = growth_post_earthquake_1
forvalues i = 2(1)5 {
    local a = `i' - 1
    gen GDP_index_post_`i' = growth_post_earthquake_`i'

}
order GDP_index_pre_5 GDP_index_pre_4 GDP_index_pre_3 GDP_index_pre_2 GDP_index_pre_1 index_earthquake GDP_index_post_1 GDP_index_post_2 GDP_index_post_3 GDP_index_post_4 GDP_index_post_5
gen a = 1
collapse (sd) GDP_index* (sd) index_earthquake* (sum) a
forvalues i = 5(-1)1 {
	gen conf_int_pre_`i' = GDP_index_pre_`i'*1.645/sqrt(a-1)
}
gen conf_int_earthquake = index_earthquake*1.645/sqrt(a-1)
forvalues i = 1(1)5 {
	gen conf_int_post_`i' = GDP_index_post_`i'*1.645/sqrt(a-1)
}
keep conf*
export excel using "$root\Charts in excel\Figure 1.xlsx", sheet("`var'") sheetmodify cell(B5)
restore
}